Introduction to Database Indexes in MySQL

Course: MySQL Training Course

Topic: Introduction to Database Indexes

Course Link: MySQL Training Course at JustAcademy

Demo Link: Register for a Course Demo

1. Introduction to Database Indexes

A database index is a data structure that helps MySQL locate records efficiently without having to examine every row in a table. Indexes are commonly used to improve the performance of SELECT queries, filtering, sorting, joining tables, and retrieving records using specific column values.

For example, imagine an employees table containing 100,000 records. If a user searches for an employee using an employee ID and there is no suitable index, MySQL may need to examine many rows to find the matching record. If an appropriate index exists, MySQL can often locate the record much more efficiently.

Indexes are an important part of database performance optimization. However, they also require storage space and must be maintained when data is inserted, updated, or deleted. Therefore, indexes should be designed according to actual query requirements.

Learning Objectives

• Understand what database indexes are and why they are used.
• Learn how MySQL uses indexes to retrieve records.
• Understand the relationship between indexes and query performance.
• Learn the difference between primary, unique, single-column, and composite indexes.
• Understand clustered and secondary indexes in InnoDB.
• Create, inspect, and remove indexes using SQL commands.
• Understand B-tree indexes and other index types.
• Learn how indexes support WHERE, JOIN, ORDER BY, and GROUP BY operations.
• Use EXPLAIN to investigate index usage.
• Identify common indexing mistakes and apply best practices.

2. What Is a Database Index?

A database index is a structure that stores indexed values in an organized form and provides a way to locate the corresponding table records. You can compare it to the index at the back of a book: instead of reading every page, you look up a keyword and find the relevant page numbers.

In MySQL, indexes are created on one or more columns or, in supported cases, expressions. The query optimizer determines whether an available index is beneficial for a particular query.

2.1 Example Without an Index

Consider the following table:

CREATE DATABASE IF NOT EXISTS company_db;
USE company_db;

CREATE TABLE employees (
    employee_id INT NOT NULL,
    employee_name VARCHAR(100),
    department VARCHAR(50),
    salary DECIMAL(10,2),
    email VARCHAR(150)
) ENGINE = InnoDB;

Suppose you want to find an employee by department:

SELECT *
FROM employees
WHERE department = 'IT';

If there is no suitable index, MySQL may perform a full table scan, examining the rows to determine which employees belong to the IT department.

2.2 Example With an Index

You can create an index on the department column:

CREATE INDEX idx_employees_department
ON employees(department);

Now MySQL has an additional structure it can use to locate rows matching the department condition. The optimizer may choose this index when it estimates that doing so is more efficient than scanning the table.

2.3 Key Characteristics of Indexes

• Indexes can improve data retrieval performance.
• They can help MySQL find rows matching WHERE conditions.
• They can improve certain joins, sorting operations, and grouping operations.
• They consume disk space and memory resources when cached.
• They add maintenance work to INSERT, UPDATE, and DELETE operations.
• Having an index does not guarantee that MySQL will use it for every query.

3. Why Are Indexes Important?

Indexes are especially useful when tables contain large amounts of data and queries repeatedly search for specific records.

3.1 Faster Data Retrieval

Indexes can reduce the number of records MySQL needs to examine when searching for matching values.

SELECT *
FROM employees
WHERE employee_id = 101;

An index on employee_id can help MySQL locate the matching employee without scanning the entire table.

3.2 Improved Filtering

Indexes can support queries that filter data using WHERE conditions.

SELECT employee_name, salary
FROM employees
WHERE department = 'Finance';

An index on department may help MySQL identify the relevant employees more efficiently, depending on the data distribution and number of matching records.

3.3 Faster Joins

Indexes on join columns can help MySQL locate matching records in related tables.

SELECT e.employee_name, d.department_name
FROM employees e
JOIN departments d
    ON e.department_id = d.department_id;

Indexes on appropriate department ID columns can help the join execute efficiently. The actual benefit depends on table sizes, indexes, and the chosen execution plan.

3.4 Sorting and Grouping

Indexes can sometimes help MySQL retrieve rows in the required order or group records efficiently.

SELECT employee_id, employee_name
FROM employees
ORDER BY employee_id;

If the ordering matches a usable index, MySQL may be able to avoid a separate sorting operation.

3.5 Improved Application Responsiveness

When frequently executed queries become more efficient, applications may respond faster and use fewer database resources. This can be valuable for e-commerce websites, banking applications, reporting systems, and customer management platforms.

4. How Indexes Work in MySQL

MySQL supports different index structures depending on the storage engine and index type. For InnoDB tables, the primary index and most ordinary secondary indexes use B-tree-based structures, specifically B+ tree implementations.

4.1 Index Lookup Process

1. The application sends an SQL query to MySQL.
2. The query optimizer evaluates possible execution plans.
3. MySQL determines whether an available index is useful for the query.
4. If an index is selected, MySQL navigates its structure to find relevant key values.
5. MySQL retrieves the corresponding records or obtains required values directly from the index when possible.
6. The query returns the results to the application.

4.2 Example of Index Lookup

CREATE INDEX idx_employees_email
ON employees(email);

SELECT *
FROM employees
WHERE email = '[email protected]';

If the optimizer chooses the index, MySQL can use the indexed email value to locate the corresponding row more efficiently than a full scan may allow.

4.3 Indexes and Selectivity

Selectivity describes how effectively a column distinguishes individual records. A column containing mostly unique values generally has higher selectivity than a column containing only a few repeated values.

For example, an employee ID is usually highly selective, while a column such as gender or a status field may contain only a small number of distinct values.

A highly selective index is often useful for finding a small number of records. However, even an index on a low-selectivity column may be useful in some queries, especially when it is combined with other columns or helps satisfy an ordering requirement.

5. Types of Indexes in MySQL

MySQL supports several index types and index constraints. Their behavior depends on the storage engine, indexed columns, and query requirements.

6. Primary Key Index

A primary key uniquely identifies every record in a table. MySQL automatically creates an index for the primary key.

6.1 Creating a Primary Key

CREATE TABLE students (
    student_id INT NOT NULL,
    student_name VARCHAR(100) NOT NULL,
    course_name VARCHAR(100),
    PRIMARY KEY (student_id)
) ENGINE = InnoDB;

The student_id column is the primary key. It cannot contain NULL values or duplicate values.

6.2 Adding a Primary Key to an Existing Table

ALTER TABLE students
ADD PRIMARY KEY (student_id);

This command works only if the existing data satisfies the primary key requirements. Duplicate or NULL values in the proposed key column can prevent the operation.

6.3 Advantages of a Primary Key

• Provides a unique identifier for each record.
• Prevents duplicate primary key values.
• Supports efficient lookups by key.
• Can be referenced by foreign keys in related tables.
• In InnoDB, the primary key is used as the clustered index.

7. Unique Index

A unique index ensures that indexed key values do not duplicate one another. It is useful for columns such as email addresses, usernames, and other values that must be unique according to application rules.

7.1 Create a Unique Index

CREATE UNIQUE INDEX idx_employees_email
ON employees(email);

This command prevents duplicate non-NULL email values. MySQL permits multiple NULL values in a unique index on a nullable column.

7.2 Example

INSERT INTO employees
(employee_id, employee_name, department, salary, email)
VALUES
(101, 'Amit Sharma', 'IT', 45000.00, '[email protected]');

INSERT INTO employees
(employee_id, employee_name, department, salary, email)
VALUES
(102, 'Neha Verma', 'HR', 40000.00, '[email protected]');

If the unique index exists and the first record has already been inserted, the second INSERT fails because it attempts to use the same non-NULL email value.

7.3 Primary Key vs. Unique Index

8. Single-Column Index

A single-column index is created on one column. It is useful when queries frequently filter, sort, or join using that column.

8.1 Create an Index

CREATE INDEX idx_employees_department
ON employees(department);

8.2 Query Using the Index

SELECT employee_id, employee_name
FROM employees
WHERE department = 'IT';

MySQL may use the index to locate employees in the IT department. The optimizer decides whether index access is more efficient than other available plans.

8.3 When to Use a Single-Column Index

• When a column is frequently used in selective WHERE conditions.
• When a column is frequently used to locate related records in joins.
• When a column is commonly used in ORDER BY operations.
• When query analysis shows that the index can reduce the work required.

9. Composite Index

A composite index, also called a multiple-column index, is created on two or more columns. The order of columns in the index is important because MySQL can generally use the leftmost prefix of the indexed column sequence.

9.1 Creating a Composite Index

CREATE INDEX idx_department_salary
ON employees(department, salary);

This index contains department as the first key part and salary as the second key part.

9.2 Query Using Both Columns

SELECT employee_name, salary
FROM employees
WHERE department = 'IT'
  AND salary > 40000;

The composite index may help MySQL find records matching the department and salary conditions.

9.3 Leftmost Prefix Rule

For an index on (department, salary), MySQL can generally use the index for lookups involving:

• department
• department, salary

The index is not generally as useful for a lookup that filters only on salary, because salary is not the leftmost column of this index. MySQL may still choose another execution strategy or use a different index if one exists.

9.4 Composite Index Example

CREATE INDEX idx_orders_customer_date
ON orders(customer_id, order_date);

SELECT *
FROM orders
WHERE customer_id = 501;

SELECT *
FROM orders
WHERE customer_id = 501
  AND order_date >= '2026-01-01';

Both queries may benefit from the composite index because they use its leftmost column. The second query also filters by the second indexed column.

9.5 Choosing Column Order

Choose composite index column order based on the queries the application actually runs. Consider equality conditions, range conditions, join requirements, sorting, grouping, and selectivity. Do not rely on a simplistic rule that the most selective column must always come first; the best order depends on the complete workload.

10. Clustered and Secondary Indexes in InnoDB

InnoDB stores table rows in the structure associated with the clustered index. The primary key is the clustered index when one is defined. If a table has no primary key, InnoDB chooses an appropriate unique non-NULL index if available; otherwise, it creates a hidden clustered index.

10.1 Clustered Index

The clustered index determines the organization of the table's row data. Because the primary key is normally the clustered index, primary key lookups can retrieve rows directly through this structure.

CREATE TABLE products (
    product_id INT NOT NULL,
    product_name VARCHAR(100),
    price DECIMAL(10,2),
    PRIMARY KEY (product_id)
) ENGINE = InnoDB;

10.2 Secondary Index

A secondary index is an additional index that is separate from the clustered index. In InnoDB, a secondary index entry contains its indexed key values and the primary key value needed to locate the corresponding clustered record.

CREATE INDEX idx_products_price
ON products(price);

This index may help queries that filter or sort by price.

10.3 Clustered Index vs. Secondary Index

11. B-Tree Indexes

B-tree-based indexes are the standard structure for most ordinary MySQL indexes, including typical InnoDB primary key, unique, and secondary indexes.

They organize key values so that MySQL can navigate through the index structure instead of scanning every indexed value sequentially.

11.1 Advantages of B-Tree Indexes

• Support equality lookups.
• Support many range queries.
• Can support sorting and ordered traversal.
• Can be used with composite indexes and leftmost-prefix lookups.
• Work well for many general-purpose database queries.

11.2 Example

CREATE INDEX idx_products_price
ON products(price);

SELECT *
FROM products
WHERE price BETWEEN 500 AND 1500;

A B-tree index on price may help MySQL locate values in the specified range efficiently.

12. Full-Text Indexes

A full-text index is designed for searching text content using MySQL's full-text search features. It is useful for article titles, product descriptions, blog content, and other text-search requirements.

12.1 Create a Full-Text Index

CREATE TABLE articles (
    article_id INT PRIMARY KEY,
    title VARCHAR(200),
    content TEXT,
    FULLTEXT INDEX ft_title_content (title, content)
) ENGINE = InnoDB;

12.2 Search Text Using MATCH and AGAINST

SELECT article_id, title
FROM articles
WHERE MATCH(title, content)
AGAINST('database indexes' IN NATURAL LANGUAGE MODE);

MySQL evaluates the search using full-text search rules. The results depend on tokenization, stopwords, minimum token length, language-related behavior, and the selected search mode.

12.3 Common Uses of Full-Text Indexes

• Searching articles and blog posts.
• Searching product descriptions.
• Finding relevant text in documentation.
• Building text-search features for websites.

13. Spatial Indexes

Spatial indexes are used for supported spatial data types and spatial queries. They can help applications search geographical and geometric information, such as locations, boundaries, and points.

13.1 Example Table

CREATE TABLE locations (
    location_id INT PRIMARY KEY,
    location_name VARCHAR(100),
    coordinates POINT NOT NULL SRID 4326,
    SPATIAL INDEX idx_coordinates (coordinates)
) ENGINE = InnoDB;

Spatial index requirements depend on the storage engine, data type, and MySQL version. For InnoDB, spatially indexed columns must satisfy the relevant requirements, including the NOT NULL restriction.

13.2 Applications of Spatial Indexes

• Finding locations near a geographical point.
• Searching for objects within a spatial region.
• Supporting geographic information systems.
• Managing map and location-based application data.

14. Prefix Indexes

A prefix index indexes only the beginning of a string column rather than its entire value. It can reduce index size for long text columns when the indexed prefix is sufficiently useful for the intended queries.

14.1 Example

CREATE INDEX idx_employee_name_prefix
ON employees(employee_name(10));

This indexes the first 10 characters of employee_name, subject to the applicable data type and engine rules.

14.2 Benefits

• Can reduce index storage requirements.
• Can improve certain lookups on long string columns.
• May reduce index maintenance costs compared with indexing a larger string value.

14.3 Limitations

• Values sharing the same prefix may not be distinguished by the prefix alone.
• Some queries may need additional row checks.
• Prefix length must comply with the storage engine's index limits.
• Prefix indexes are not appropriate for every string-search requirement.

15. Functional Indexes

A functional index indexes the result of a supported expression rather than only a column's stored value. It can help queries that filter using the same expression.

15.1 Example

CREATE INDEX idx_employee_name_lower
ON employees ((LOWER(employee_name)));

In supported MySQL versions, this can help queries that use a matching expression:

SELECT *
FROM employees
WHERE LOWER(employee_name) = 'amit sharma';

Functional indexes have restrictions on which expressions can be indexed. Their usefulness depends on the query expression matching the indexed expression in a form the optimizer can recognize.

16. Creating Indexes Using CREATE INDEX

The CREATE INDEX statement adds an index to an existing table.

16.1 General Syntax

CREATE INDEX index_name
ON table_name (column_name);

16.2 Example

CREATE INDEX idx_employees_salary
ON employees(salary);

This creates an index named idx_employees_salary on the salary column.

16.3 Creating a Composite Index

CREATE INDEX idx_department_salary
ON employees(department, salary);

16.4 Creating a Unique Index

CREATE UNIQUE INDEX idx_employees_email
ON employees(email);

Creating a unique index fails if existing non-NULL values violate the uniqueness requirement.

17. Creating Indexes During Table Creation

Indexes can also be declared within a CREATE TABLE statement. This is useful when designing a table from the beginning.

CREATE TABLE customers (
    customer_id INT NOT NULL,
    customer_name VARCHAR(100) NOT NULL,
    email VARCHAR(150),
    city VARCHAR(80),
    PRIMARY KEY (customer_id),
    UNIQUE KEY uq_customers_email (email),
    KEY idx_customers_city (city)
) ENGINE = InnoDB;

This table defines a primary key, a unique index on email, and a regular index on city. Multiple NULL values may be permitted in the nullable email column despite the unique index.

17.1 Benefits of Defining Indexes During Table Creation

• The table structure and indexing requirements are documented together.
• Required uniqueness rules are established from the beginning.
• The database schema can be easier to maintain.
• Indexes are available for queries once table creation is complete.

18. Viewing Existing Indexes

Before adding a new index, inspect the indexes that already exist. This helps avoid unnecessary or duplicate indexes.

18.1 Using SHOW INDEX

SHOW INDEX FROM employees;

This displays information about the indexes defined on the employees table, including index names, indexed columns, uniqueness, and column sequence.

18.2 Using SHOW CREATE TABLE

SHOW CREATE TABLE employees;

This displays the table definition, including its declared keys and indexes.

18.3 Using INFORMATION_SCHEMA

SELECT
    TABLE_SCHEMA,
    TABLE_NAME,
    INDEX_NAME,
    COLUMN_NAME,
    NON_UNIQUE,
    SEQ_IN_INDEX
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = 'company_db'
  AND TABLE_NAME = 'employees'
ORDER BY INDEX_NAME, SEQ_IN_INDEX;

This query displays index metadata for the selected table. It can be useful for auditing indexes across a database schema.

19. Removing an Index

Indexes that are no longer useful can be removed to reduce storage and write overhead. Before dropping an index, verify that it is not needed for important queries, uniqueness requirements, or foreign key constraints.

19.1 Syntax

DROP INDEX index_name
ON table_name;

19.2 Example

DROP INDEX idx_employees_salary
ON employees;

This removes the named index from the employees table. It does not delete the employee records.

19.3 Removing a Primary Key

ALTER TABLE employees
DROP PRIMARY KEY;

Dropping a primary key is different from dropping an ordinary index. It can affect foreign key relationships and, for InnoDB, the table's clustered index organization. Check dependencies and table design before performing this operation.

20. How Indexes Improve WHERE Conditions

Indexes can help MySQL find rows that match conditions in the WHERE clause. Their effectiveness depends on the operator, indexed columns, data distribution, and execution plan.

20.1 Equality Condition

CREATE INDEX idx_employees_department
ON employees(department);

SELECT *
FROM employees
WHERE department = 'IT';

The index may be useful when the query retrieves a relatively small subset of the table.

20.2 Range Condition

CREATE INDEX idx_employees_salary
ON employees(salary);

SELECT *
FROM employees
WHERE salary BETWEEN 30000 AND 50000;

A B-tree index may help locate the salary range without scanning all rows, although the optimizer may choose a table scan if many records match.

20.3 Multiple Conditions

CREATE INDEX idx_department_salary
ON employees(department, salary);

SELECT *
FROM employees
WHERE department = 'IT'
  AND salary > 40000;

The composite index may support the equality condition on department and the range condition on salary.

20.4 Conditions That May Reduce Index Usefulness

Some query patterns can make an ordinary index less useful. For example, applying a function to an indexed column may prevent the optimizer from using a regular index for a direct lookup.

SELECT *
FROM employees
WHERE YEAR(hire_date) = 2025;

A more index-friendly alternative is often to use a date range:

SELECT *
FROM employees
WHERE hire_date >= '2025-01-01'
  AND hire_date < '2026-01-01';

This form can allow an index on hire_date to be considered. Always inspect the actual execution plan rather than assuming a specific index will be selected.

21. Indexes for JOIN Operations

Indexes can improve joins by helping MySQL locate matching records in related tables. The join columns should have compatible data types, and indexes should match the query's access patterns.

21.1 Example Tables

CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(100) NOT NULL
) ENGINE = InnoDB;

CREATE TABLE employee_records (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(100) NOT NULL,
    department_id INT,
    INDEX idx_employee_records_department (department_id),
    CONSTRAINT fk_employee_department
        FOREIGN KEY (department_id)
        REFERENCES departments(department_id)
) ENGINE = InnoDB;

21.2 Join Query

SELECT
    e.employee_name,
    d.department_name
FROM employee_records e
JOIN departments d
    ON e.department_id = d.department_id;

The primary key on departments.department_id and the index on employee_records.department_id can support efficient matching. InnoDB also requires suitable indexes for foreign key relationships and may create a needed referencing-column index automatically if one is missing.

22. Indexes for ORDER BY and GROUP BY

Indexes can help with sorting and grouping when the query's column order matches a usable index. They do not eliminate every sorting operation.

22.1 ORDER BY Example

CREATE INDEX idx_employees_department_name
ON employees(department, employee_name);

SELECT employee_id, employee_name, department
FROM employees
WHERE department = 'IT'
ORDER BY employee_name;

Because the query filters on department and orders by employee_name, the composite index may help satisfy both operations.

22.2 GROUP BY Example

SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department;

An index on department may help some grouping plans, depending on the table size, distribution, and execution strategy.

23. Understanding EXPLAIN for Index Analysis

EXPLAIN displays the execution plan MySQL expects to use for a query. It helps developers investigate whether an index is available and how MySQL intends to access table data.

23.1 Basic Syntax

EXPLAIN
SELECT *
FROM employees
WHERE department = 'IT';

23.2 Important EXPLAIN Columns

23.3 Understanding Common Access Types

• ALL: A full table scan.
• const: A table access where a unique or primary key lookup can identify at most one matching row in the applicable plan.
• eq_ref: A unique-key lookup used for each combination of rows from preceding tables in a join.
• ref: An index lookup using a nonunique key or a leftmost prefix of a key.
• range: An index range scan.
• index: A full index scan rather than a full table scan.

A full table scan is not automatically a problem. It can be the best plan for a small table or a query that needs most of its rows.

23.4 EXPLAIN with a Composite Index

CREATE INDEX idx_department_salary
ON employees(department, salary);

EXPLAIN
SELECT employee_name, salary
FROM employees
WHERE department = 'IT'
  AND salary > 40000;

Review the key and access type in the output. If MySQL chooses a different plan, consider table size, statistics, selectivity, and the number of matching rows before changing the index design.

24. Covering Indexes

A covering index contains all the indexed and selected column values needed to satisfy a query, so MySQL can sometimes return the result directly from the index without reading the full table row.

24.1 Example

CREATE INDEX idx_department_name
ON employees(department, employee_name);

SELECT department, employee_name
FROM employees
WHERE department = 'IT';

The index includes both columns used by the query. MySQL may be able to use it as a covering index, depending on the chosen execution plan.

24.2 Benefits of Covering Indexes

• May reduce access to the clustered table records.
• Can reduce data retrieval work for frequently executed queries.
• Can improve some reporting and lookup workloads.

24.3 Limitations

• Adding more indexed columns increases index size.
• Wider indexes consume more storage and cache space.
• Every additional indexed value adds maintenance overhead for data changes.
• Covering indexes should be designed for important query patterns rather than every possible query.

25. Indexes and Query Performance

Indexes can significantly improve certain queries, but their impact depends on the amount of data, selectivity, query structure, available memory, storage performance, and optimizer decisions.

25.1 Factors That Influence Index Performance

• Table size and row count.
• Number of distinct values in indexed columns.
• Percentage of rows returned by the query.
• Index column order and query predicates.
• Availability of suitable indexes for joins and sorting.
• Table statistics and optimizer estimates.
• Storage engine and physical data layout.
• Cost of maintaining indexes during data modifications.

25.2 Why an Index May Not Be Used

• The table is small enough that a full scan is cheaper.
• The query returns a large percentage of the rows.
• The indexed column has low selectivity for the query.
• The WHERE condition does not match the usable index structure.
• The query applies a function or conversion that prevents ordinary index lookup.
• The optimizer estimates that another execution plan is more efficient.
• The index or table statistics do not reflect the current data distribution accurately.

26. Disadvantages of Database Indexes

Although indexes improve many read operations, adding too many indexes can reduce overall database performance.

26.1 Additional Storage

Every index requires storage for its key values and associated information. Tables with many indexes may consume substantially more disk space.

26.2 Slower INSERT Operations

When a row is inserted, MySQL must maintain relevant indexes. A table with many indexes can require more work for each insertion.

26.3 Slower UPDATE Operations

If an UPDATE changes an indexed column, the corresponding index entries may need to be updated. This increases the cost of modifying the data.

26.4 Slower DELETE Operations

Deleting a row requires MySQL to maintain the table's associated indexes. A large number of indexes can increase deletion overhead.

26.5 Additional Maintenance

Indexes need to be reviewed as application queries and data distributions change. Unused, redundant, or poorly designed indexes can waste resources.

27. Common Indexing Mistakes

28. Best Practices for Database Indexing

1. Index frequently searched columns: Prioritize columns used in important WHERE conditions and joins.
2. Review query patterns: Design indexes based on real application queries rather than guesses.
3. Use primary keys thoughtfully: Choose a stable, suitable primary key for InnoDB tables.
4. Use unique indexes for data rules: Enforce uniqueness in the database when it is part of the data model.
5. Design composite indexes carefully: Consider leftmost-prefix behavior and the complete query workload.
6. Avoid unnecessary indexes: Each index adds storage and data modification costs.
7. Inspect execution plans: Use EXPLAIN and, where appropriate, EXPLAIN ANALYZE to investigate performance.
8. Keep statistics current: Consider ANALYZE TABLE when index or data distribution changes make optimizer statistics stale.
9. Test with realistic data: An index that works well on a small test table may behave differently on production-sized data.
10. Consider covering indexes: For important queries, determine whether an index can provide all required columns without becoming unnecessarily wide.
11. Monitor write performance: Evaluate INSERT, UPDATE, and DELETE overhead when adding indexes.
12. Review indexes periodically: Remove unused or redundant indexes only after checking constraints and application usage.

29. Practical Example: Building an Indexed Employee Database

This exercise combines table creation, index creation, query execution, and index inspection.

29.1 Create the Table

CREATE TABLE IF NOT EXISTS employee_directory (
    employee_id INT NOT NULL,
    employee_name VARCHAR(100) NOT NULL,
    department VARCHAR(50),
    salary DECIMAL(10,2),
    email VARCHAR(150),
    hire_date DATE,
    PRIMARY KEY (employee_id),
    UNIQUE KEY uq_employee_email (email),
    KEY idx_employee_department (department),
    KEY idx_employee_hire_date (hire_date)
) ENGINE = InnoDB;

29.2 Insert Sample Records

INSERT INTO employee_directory
(employee_id, employee_name, department, salary, email, hire_date)
VALUES
(1, 'Amit Sharma', 'IT', 45000.00, '[email protected]', '2023-04-10'),
(2, 'Neha Verma', 'HR', 40000.00, '[email protected]', '2022-08-15'),
(3, 'Rahul Mehta', 'IT', 52000.00, '[email protected]', '2021-06-20'),
(4, 'Priya Singh', 'Finance', 48000.00, '[email protected]', '2024-01-12'),
(5, 'Karan Joshi', 'Sales', 37000.00, '[email protected]', '2023-09-05');

29.3 Query Employees by Department

SELECT employee_id, employee_name
FROM employee_directory
WHERE department = 'IT';

The department index may support this query.

29.4 Query Employees by Hire Date

SELECT employee_name, hire_date
FROM employee_directory
WHERE hire_date >= '2023-01-01'
  AND hire_date < '2024-01-01';

The hire_date index may help locate records within the specified date range.

29.5 Inspect the Execution Plan

EXPLAIN
SELECT employee_id, employee_name
FROM employee_directory
WHERE department = 'IT';

Review the selected key and other plan details. Because this example contains only a few records, MySQL may choose a table scan instead of the index. That can be a reasonable choice for a small table.

29.6 View All Indexes

SHOW INDEX FROM employee_directory;

30. Practical Exercises

Exercise 1: Create a Single-Column Index

Create a table named products and add an index on product_name. Run a query that searches for a particular product and inspect its execution plan.

Exercise 2: Create a Unique Index

Create a unique index on an email column. Test what happens when two records attempt to use the same non-NULL email value.

Exercise 3: Create a Composite Index

Create an index on department and salary. Test queries that filter by department alone, by department and salary, and by salary alone. Compare the execution plans.

Exercise 4: Understand Index Removal

Create an ordinary index, inspect it with SHOW INDEX, and remove it with DROP INDEX. Confirm that the table data remains intact.

Exercise 5: Analyze a Date Query

Create an index on hire_date. Compare the execution plans for a YEAR(hire_date) condition and an equivalent date-range condition.

Exercise 6: Investigate Covering Indexes

Create a composite index that includes the columns needed by a frequently used query. Check whether EXPLAIN reports that the index can cover the query.

31. Mini Project: Product Search and Query Optimization

Project Objective: Design a product table with suitable indexes for product searches, category filtering, price ranges, and sorting.

Project Requirements

• Create a products table using InnoDB.
• Add a primary key to identify products.
• Add a unique index to a product SKU column.
• Create an index for category-based filtering.
• Create a composite index for a suitable category and price query.
• Insert sample product records.
• Use EXPLAIN to inspect query plans.
• Compare index usage for equality, range, and sorting queries.
• Review the trade-off between query speed and index maintenance.

Sample SQL

CREATE TABLE IF NOT EXISTS products (
    product_id INT NOT NULL,
    product_name VARCHAR(150) NOT NULL,
    sku VARCHAR(50) NOT NULL,
    category VARCHAR(80),
    price DECIMAL(10,2) NOT NULL,
    PRIMARY KEY (product_id),
    UNIQUE KEY uq_products_sku (sku),
    KEY idx_products_category_price (category, price)
) ENGINE = InnoDB;

INSERT INTO products
(product_id, product_name, sku, category, price)
VALUES
(1, 'Wireless Mouse', 'WM-101', 'Accessories', 799.00),
(2, 'Mechanical Keyboard', 'KB-102', 'Accessories', 2499.00),
(3, 'Laptop Stand', 'LS-103', 'Office', 1299.00),
(4, 'USB Hub', 'UH-104', 'Accessories', 999.00),
(5, 'Office Chair', 'OC-105', 'Office', 5499.00);

SELECT product_name, price
FROM products
WHERE category = 'Accessories'
  AND price < 2000
ORDER BY price;

EXPLAIN
SELECT product_name, price
FROM products
WHERE category = 'Accessories'
  AND price < 2000
ORDER BY price;

SHOW INDEX FROM products;

Expected Learning Outcomes

• Understand how indexes support product lookup operations.
• Learn how composite indexes can support multiple query conditions.
• Read basic EXPLAIN output.
• Understand why the optimizer may choose different plans for small and large tables.
• Balance retrieval performance with index storage and maintenance costs.

32. Interview Questions and Answers

Q1. What is a database index?

A database index is a structure that helps MySQL locate records efficiently without examining every row in the table for many types of queries.

Q2. Why are indexes used?

Indexes can improve filtering, joins, sorting, grouping, and record retrieval, depending on the query and data distribution.

Q3. What is the difference between a primary key and a unique index?

A primary key uniquely identifies each row and cannot contain NULL values. A unique index enforces uniqueness but can allow multiple NULL values when the indexed column permits NULL.

Q4. What is a composite index?

A composite index is an index built on two or more columns in a defined order.

Q5. What is the leftmost-prefix rule?

For a composite index, MySQL can generally use a leftmost sequence of indexed columns for lookups. An index on (A, B, C) can generally support lookups on A, A and B, or A, B and C.

Q6. What is a clustered index in InnoDB?

A clustered index organizes the table's row data. InnoDB uses the primary key as the clustered index when a primary key is defined.

Q7. What is a secondary index?

A secondary index is an additional index separate from the clustered index. In InnoDB, it includes the indexed key values and the primary key used to locate the clustered row.

Q8. What is a B-tree index?

A B-tree-based index organizes key values for efficient lookup, range access, and ordered traversal. InnoDB uses B+ tree implementations for ordinary indexes.

Q9. What is a full-text index?

A full-text index supports text searching through features such as MATCH and AGAINST.

Q10. What is a spatial index?

A spatial index supports certain spatial searches on supported geographic or geometric data.

Q11. What is a covering index?

A covering index contains all columns needed for a query, allowing MySQL in suitable cases to retrieve results from the index without reading the full table rows.

Q12. How do you create an index?

CREATE INDEX index_name
ON table_name(column_name);

Q13. How do you view indexes on a table?

SHOW INDEX FROM table_name;

Q14. How do you remove an index?

DROP INDEX index_name
ON table_name;

Q15. What is EXPLAIN used for?

EXPLAIN displays the query execution plan and helps investigate table access methods and index selection.

Q16. Can indexes slow down database operations?

Yes. Indexes consume storage and require maintenance during INSERT, UPDATE, and DELETE operations.

Q17. Why might MySQL ignore an index?

The table may be small, the query may return many rows, the index may not match the query conditions, or the optimizer may estimate that another plan is cheaper.

Q18. What is a prefix index?

A prefix index indexes the first specified number of characters or bytes of a string column, where supported.

Q19. What is a functional index?

A functional index stores index entries based on a supported expression, such as LOWER(email), so compatible queries may use it.

Q20. What is the best indexing strategy?

The best strategy depends on the application's query workload. Create indexes for important searches, joins, sorting, and constraints, then verify their usefulness with execution plans and realistic testing.

33. Quick Revision Checklist

• A database index helps MySQL locate records efficiently.
• Indexes can improve WHERE conditions, joins, sorting, and grouping.
• MySQL does not necessarily use an index for every query.
• Primary keys automatically have an index.
• Unique indexes enforce uniqueness.
• Composite indexes contain multiple columns in a specific order.
• The leftmost-prefix rule is important for composite indexes.
• InnoDB uses a clustered index for the table's row data.
• Secondary indexes provide additional lookup paths.
• B-tree indexes support many equality and range lookups.
• Full-text indexes support text-search operations.
• Spatial indexes support applicable spatial queries.
• Prefix indexes can reduce index size for suitable string columns.
• Functional indexes can support queries that use matching expressions.
• SHOW INDEX displays index metadata.
• CREATE INDEX adds an index to an existing table.
• DROP INDEX removes an ordinary index.
• EXPLAIN helps investigate the query execution plan.
• Too many indexes increase storage and write overhead.
• Index design should be based on measured query requirements.

34. Conclusion

Database indexes are essential tools for improving MySQL query performance. They provide efficient lookup paths for many common operations, including filtering, joining, sorting, grouping, and retrieving records by key. MySQL supports several index types, including primary key, unique, single-column, composite, full-text, spatial, prefix, and functional indexes.

Creating an index is only one part of database optimization. Developers must also understand composite index ordering, selectivity, storage engine behavior, execution plans, and the cost of maintaining indexes during data modifications. Using EXPLAIN, inspecting existing indexes, and testing with realistic workloads help ensure that indexes provide measurable benefits.

By applying these concepts, developers can build MySQL databases that retrieve information efficiently while balancing storage requirements and write performance.

Explore the Course: Learn database concepts, SQL, and MySQL query optimization through the JustAcademy MySQL Training Course.

Register for a Demo: Visit JustAcademy Course Demo Registration to learn more about the training.